package com.testingedu.jdbcDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.db.DBManager;
import com.testingedu.model.TMenu;
import com.testingedu.model.TOperate;
import com.testingedu.model.TUser;

public class TOperateDao extends BaseDao<TOperate> {
	private static final List<String> baseURIs = new ArrayList<String>();

	static {
		init();
	}

	public static void main(String[] args) {
		new TOperateDao();
	}

	public static void init() {
		Connection conn = DBManager.getConnection();
		String sql = "select too.url from t_operate too where too.menu_id in (select tm.id from t_menu tm where tm.type = 1)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				baseURIs.add(rs.getString("url"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public TOperateDao() {
		clazz = TOperate.class;
	}

	public void list(QueryResult queryResult, TUser tUser, Integer roleId,
			TOperate tOperate) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_operate tp where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tUser != null) {
			if (tUser.getName() != null && tUser.getName().trim().length() > 0) {
				sql.append(" and tp.name = ?");
				values.add(tUser.getName());
			}
		}
		if (roleId != null && roleId > 0) {
			sql
					.append(" and tp.id in (select _toir.operate_id from t_operate_in_role _toir where _toir.role_id = ?)");
			values.add(roleId);
		}
		if (tOperate != null) {
			if (tOperate.getName() != null
					&& tOperate.getName().trim().length() > 0) {
				sql.append(" and tp.name like ?");
				values.add("%" + tOperate.getName() + "%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			rs.next();
			queryResult.setAllCount(new Long(rs.getInt(1)));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void add(TUser tUser) throws Exception{
		String sql = "insert into t_operate(account,password,name,photo_path,description,certificate_id,certificate_name) values(?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getAccount());
		values.add(tUser.getPassword());
		values.add(tUser.getName());
		values.add(tUser.getPhotoPath());
		values.add(tUser.getDescription());
		values.add(tUser.getCertificateId());
		values.add(tUser.getCertificateName());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void update(TUser tUser) throws Exception{
		String sql = "update t_operate set name = ?, description = ? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getName());
		values.add(tUser.getDescription());
		values.add(tUser.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void delete(int id) throws Exception{
		String sql = "delete from t_operate tp where tp.id = ?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TOperate info(int id) throws Exception{
		String sql = "select * from t_operate tp where tp.id = ?";
		List<TOperate> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public static boolean validatePermission(String url, Integer userId) throws Exception{
		if (baseURIs.contains(url)) {
			return true;
		}
		String sql = "select too.id from t_operate too where too.url = ? and too.id in (select toir.operate_id from t_operate_in_role toir where toir.role_id in (select triu.role_id from t_role_in_user triu where "
				+ "triu.user_id = ?))";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBManager.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.setInt(2, userId);
			rs = ps.executeQuery();
			if(rs.next())
				return true;
		} catch (Exception e) {
			throw new Exception();
		}
		finally{
			if(rs != null)
				rs.close();
			if(ps != null)
				ps.close();
			if(conn != null)
				conn.close();
		}
		return false;
	}

	public List<TOperate> _myOperate(Integer userId, QueryResult queryResult,
			TOperate tOperate) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_operate too where too.id in (select distinct(toir.operate_id) from t_operate_in_role toir where toir.role_id in (select triu.role_id from t_role_in_user triu where triu.user_id = ?))");
		List<Object> values = new ArrayList<Object>();
		values.add(userId);
		if (tOperate != null) {
			if (tOperate.getName() != null
					&& tOperate.getName().trim().length() > 0) {
				sql.append(" and too.name like ?");
				values.add("%" + tOperate.getName() + "%");
			}
		}
		List<TOperate> result = null;
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			result = _resultSetToList(rs);
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			if (rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		return result;
	}

	public List<TOperate> _tRoleOperate(Integer roleId) throws Exception{
		String sql = "select * from t_operate too where too.id in (select distinct(toir.operate_id) from t_operate_in_role toir where toir.role_id = ?)";
		List<Object> values = new ArrayList<Object>();
		values.add(roleId);
		List<TOperate> result = null;
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, values);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		return result;
	}

	public void _myOperate(QueryResult queryResult, Integer userId,
			Integer roleId, TOperate tOperate) throws Exception{
		List<TOperate> myOperate = this._myOperate(userId, queryResult,
				tOperate);
		List<TOperate> tRoleOperate = this._tRoleOperate(roleId);
		List<TOperate> results = new ArrayList<TOperate>();
		for (TOperate to : myOperate) {
			boolean isOwn = false;
			if (tRoleOperate == null || tRoleOperate.size() == 0) {
				to.setIsOwn('n');
			} else {
				for (TOperate target : tRoleOperate) {
					if (to.getId().equals(target.getId())) {
						isOwn = true;
						break;
					}
				}
			}
			if (isOwn) {
				to.setIsOwn('y');
			} else {
				to.setIsOwn('n');
			}
			results.add(to);
		}
		queryResult.setResultList(results);
	}

	public List<TOperate> _resultSetToList(ResultSet rs) throws Exception{
		List<TOperate> result = new ArrayList<TOperate>();
		TMenuDao tMenuDao = new TMenuDao();
		try {
			while (rs.next()) {
				TOperate tOperate = new TOperate();
				tOperate.setId(rs.getInt("id"));
				tOperate.setName(rs.getString("name"));
				tOperate.setUrl(rs.getString("url"));
				tOperate.setDescription(rs.getString("description"));
				Integer menuId = rs.getInt("menu_id");
				TMenu tMenu = tMenuDao.info(menuId);
				tOperate.settMenu(tMenu);
				result.add(tOperate);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
}